package com.sz.ticket.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import net.sf.json.JSONSerializer;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

import com.sz.common.ICommonDAO;
import com.sz.common.SZUtility;

public class ExportTicketServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected final Log logger = LogFactory.getLog(getClass());
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
	}
	
	@SuppressWarnings("unchecked")
	public void doPost(HttpServletRequest request, HttpServletResponse response)
		throws ServletException, IOException {
		
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet rs = null;
		
		WritableWorkbook workbook = null;
		ServletOutputStream outstream = null;
		try {		
			
			//SimpleDateFormat df = new SimpleDateFormat();			
			
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-disposition", "attachment;filename=导出结果.xls");
			outstream = response.getOutputStream();
			request.setCharacterEncoding("utf-8");
			
			workbook = Workbook.createWorkbook(outstream);
			WritableSheet sheet = workbook.createSheet("sheet1", 0);			
			WritableCellFormat titleFormat = createFormat(14, jxl.format.Alignment.CENTRE, Colour.LIGHT_GREEN, true);
			WritableCellFormat dataFormat = createFormat(12, jxl.format.Alignment.LEFT, Colour.WHITE, false);
			
			String paramValue = request.getParameter("PARAM_VALUE");
			String title_field = request.getParameter("TITLE_FIELD");
			String ids = request.getParameter("BUSINESS_ID_LIST"); //选择商家导出会传过来商家ID列表
			String[] array = title_field.split(",");
			
			Map map = (Map)JSONSerializer.toJSON(paramValue);
			map.put("BUSINESS_ID_LIST", ids); //选择商家导出会传过来商家ID列表
			String sql = getSql(map);
			logger.info(sql);			
			
			//title field
			List titles = new ArrayList();
			List fields = new ArrayList();
			for(int i = 0; i < array.length; ++i) {
				String[] tmp = array[i].split(":");
				if(tmp.length == 2) {
					titles.add(tmp[0]);
					fields.add(tmp[1]);
				}
			}
			
			for(int i = 0; i < titles.size(); ++i) {
				sheet.addCell(new Label(i, 0, titles.get(i).toString(),titleFormat));
				// 设置列宽
				sheet.setColumnView(i, 20);
			}
			
			ServletContext servletContext = request.getSession().getServletContext();        
			ApplicationContext context = WebApplicationContextUtils.getWebApplicationContext(servletContext);
			ICommonDAO dao = (ICommonDAO)context.getBean("CommonDAO");
			conn = dao.getConnection();
			stm = conn.prepareStatement(sql);
			rs = stm.executeQuery();			
			
			int row = 1;
			while(rs.next()) {				
				for(int i = 0; i < fields.size(); ++i) {
					String field = fields.get(i).toString();
					String s = rs.getString(field);
					s = addition(field, s); //处理一些字典值
					sheet.addCell(new Label(i, row, s, dataFormat));
				}
				
				++row;
			}
			
			workbook.write();
		
		} catch (Exception e) {
			logger.error(e.getMessage());
			throw new ServletException(e.getMessage());
		} finally {			
			try {
				if(rs != null) rs.close();
				if(stm != null) stm.close();
				if(conn != null) conn.close();
				
				if(workbook != null) workbook.close();
				
				if(outstream != null) {
					outstream.flush();
					outstream.close();
				}
				
				
			} catch (Exception e) {
				
			}			
		}
		
		
	
	}
	
	@SuppressWarnings("unchecked")
	private String getSql(Map map) {
		String status 				= SZUtility.toString(map.get("STATUS"));
		String check_b_flag 		= SZUtility.toString(map.get("CHECK_B_FLAG"));
		String check_c_flag 		= SZUtility.toString(map.get("CHECK_C_FLAG"));
		String create_time_begin 	= SZUtility.toString(map.get("CREATE_TIME_BEGIN"));
		String create_time_end		= SZUtility.toString(map.get("CREATE_TIME_END"));
		String demand_time_begin	= SZUtility.toString(map.get("DEMAND_TIME_BEGIN"));
		String demand_time_end		= SZUtility.toString(map.get("DEMAND_TIME_END"));
		String b_business_id		= SZUtility.toString(map.get("B_BUSINESS_ID"));
		String b_group_id			= SZUtility.toString(map.get("B_GROUP_ID"));
		String b_name				= SZUtility.toString(map.get("B_NAME"));
		String business_code		= SZUtility.toString(map.get("BUSINESS_CODE"));
		String b_region_id			= SZUtility.toString(map.get("B_REGION_ID"));
		
		String ids					= SZUtility.toString(map.get("BUSINESS_ID_LIST")); 
		if(ids.length() > 0) {
			//ids不为"",就认为是选择商家的导出
			b_business_id = ids;
		}
		
		StringBuffer buf = new StringBuffer();
		buf.append("select b.business_id b_id, b.name b_name, b.tel1 b_tel,");
		buf.append("b.check_notice b_notice, b.check_status b_check_status,");
		buf.append("t.*,");
		buf.append("to_char(t.create_time, 'yyyy-mm-dd hh24:mi:ss') create_time_str,");
		buf.append("to_char(t.demand_time, 'yyyy-mm-dd hh24') demand_time_str,");
		buf.append("t.position_flag||','||t.nosmoking_flag||','||t.window_flag||','||t.baby_flag||','||t.wc_flag||',A' demand_str, "); //demand_str ',A'是因为发现split的问题        
		buf.append("b_dic_check.dic_text check_b_flag_str, c_dic_check.dic_text check_c_flag_str,");
		buf.append("m.name create_manager_name, b_m.name check_b_manager_name, c_m.name check_c_manager_name ");
		buf.append("from business b, ticket t, dic_cfg b_dic_check, dic_cfg c_dic_check, manager m, manager b_m, manager c_m ");
		buf.append("where b.business_id = t.business_id ");
		buf.append("and b_dic_check.dic_name = 'CHECK_FLAG' and b_dic_check.dic_value(+) = t.check_b_flag ");
		buf.append("and c_dic_check.dic_name = 'CHECK_FLAG' and c_dic_check.dic_value(+) = t.check_c_flag ");
		buf.append("and m.manager_id(+) = t.create_manager ");
		buf.append("and b_m.manager_id(+) = t.check_b_manager ");
		buf.append("and c_m.manager_id(+) = t.check_c_manager ");
		if(status.length() > 0) {
			buf.append(" and t.status = " + status);
		}
		if(check_b_flag.length() > 0) {
			buf.append(" and t.check_b_flag = " + check_b_flag);
		}
		if(check_c_flag.length() > 0) {
			buf.append(" and t.check_c_flag = " + check_c_flag);
		}
		if(create_time_begin.length() > 0) {
			buf.append(String.format(" and trunc(t.create_time) >= to_date('%s', 'yyyy-mm-dd')", create_time_begin));
		}
		if(create_time_end.length() > 0) {
			buf.append(String.format(" and trunc(t.create_time) <= to_date('%s', 'yyyy-mm-dd')", create_time_end));
		}
		if(demand_time_begin.length() > 0) {
			buf.append(String.format(" and trunc(t.demand_time) >= to_date('%s', 'yyyy-mm-dd')", demand_time_begin));
		}
		if(demand_time_end.length() > 0) {
			buf.append(String.format(" and trunc(t.demand_time) <= to_date('%s', 'yyyy-mm-dd')", demand_time_end));
		}
		if(b_business_id.length() > 0) {
			buf.append(" and b.business_id in(" + b_business_id + ")");
		}
		if(b_group_id.length() > 0) {
			buf.append(" and b.group_id = " + b_group_id);
		}
		if(b_name.length() > 0) {
			buf.append(String.format(" and b.name like '%%%s%%'", b_name));
		}
		if(business_code.length() > 0) {
			buf.append(String.format(" and b.business_code like '%%%s%%'", business_code));
		}
		if(b_region_id.length() > 0) {
			buf.append("  and exists (select r.region_id from region r where b.region_id = r.region_id ");
			buf.append(" start with r.region_id = " + b_region_id);
			buf.append(" connect by r.parent_id = prior region_id) ");
		}
		
		buf.append(" order by t.business_id");
		
		return buf.toString();
	}
	
	private String addition(String fn, String fv) {
		if(fn==null || fv==null) return "";
		
		if(fn.compareToIgnoreCase("B_CHECK_STATUS") == 0) {
			if(fv.equals("0")) {
				fv = "未签约";
			} else if(fv.equals("1")) {
				fv = "已签约";
			}
		}
		else if(fn.compareToIgnoreCase("POSITION_FLAG") == 0) {
			if(fv.equals("0")) {
				fv = "";
			} else if(fv.equals("1")) {
				fv = "大厅";
			} else if(fv.equals("2")) {
				fv = "包房";
			}
		}
		
		else if(fn.compareToIgnoreCase("CHECK_B_ENV") == 0) {
			if(fv.equals("0")) {
				fv = "";
			} else if(fv.equals("1")) {
				fv = "大厅";
			} else if(fv.equals("2")) {
				fv = "包房";
			}
		}
		
		else if(fn.compareToIgnoreCase("NO_SMOKING_FLAG") == 0) {
			if(fv.equals("1")) {
				fv = "无烟区";
			} else {
				fv = "";
			}
		}
		else if(fn.compareToIgnoreCase("WINDOW_FLAG") == 0) {
			if(fv.equals("1")) {
				fv = "靠窗";
			} else {
				fv = "";
			}
		}
		else if(fn.compareToIgnoreCase("BABY_FLAG") == 0) {
			if(fv.equals("1")) {
				fv = "宝宝椅";
			} else {
				fv = "";
			}
		}
		else if(fn.compareToIgnoreCase("WC_FLAG") == 0) {
			if(fv.equals("1")) {
				fv = "卫生间";
			} else {
				fv = "";
			}
		}
		
		else if(fn.compareToIgnoreCase("DEMAND_STR") == 0) {			
			String[] tmp = fv.split(",");
			if(tmp.length == 6) {
				fv = "";
				String p = addition("POSITION_FLAG", tmp[0]);
				String smoking = addition("NO_SMOKING_FLAG", tmp[1]);
				String win = addition("WINDOW_FLAG", tmp[2]);
				String baby = addition("BABY_FLAG", tmp[3]);
				String wc = addition("WC_FLAG", tmp[4]);
				
				if(p.length() > 0) {fv+=p; fv+=",";}
				if(smoking.length() > 0) {fv+=smoking; fv+=",";}
				if(win.length() > 0) {fv+=win; fv+=",";}
				if(baby.length() > 0) {fv+=baby; fv+=",";}
				if(wc.length() > 0) {fv+=wc; fv+=",";}
				
				if(fv.length() > 0 && fv.substring(fv.length()-1).equals(",")) {
					fv = fv.substring(0, fv.length()-1);
				}
				
				
			}
		}
		return fv;
	}
	
	private WritableCellFormat createFormat(int size, Alignment align,
			Colour colour, boolean isBold) throws WriteException {
		WritableFont timesBold = new WritableFont(
				WritableFont.createFont("宋体"), size, isBold ? WritableFont.BOLD
						: WritableFont.NO_BOLD, false);
		WritableCellFormat fontSubject = new WritableCellFormat(timesBold);
		fontSubject.setAlignment(align);
		fontSubject.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		fontSubject.setBorder(jxl.format.Border.ALL,
				jxl.format.BorderLineStyle.THIN);
		fontSubject.setBackground(colour);
		return fontSubject;
	}
}
